#!/usr/bin/env node

const Database = require('better-sqlite3');
const db = new Database('./data/delivery-management.db');

console.log('🧹 清理冗余实例表...\n');

try {
  // 专业前缀映射
  const specialtyPrefixes = {
    '配管专业': 'piping',
    '电气专业': 'electrical',
    '暖通专业': 'hvac',
    '通信专业': 'communication',
    '仪表专业': 'instrumentation'
  };

  console.log('1. 识别标准实例表...');
  
  // 获取标准表名列表
  const objectClasses = db.prepare(`
    SELECT DISTINCT
      dtm.classification_name,
      dtm.specialty,
      dtm.english_name
    FROM device_type_mappings dtm
    WHERE dtm.classification_name IS NOT NULL 
      AND dtm.classification_name != ''
      AND dtm.english_name IS NOT NULL
      AND dtm.english_name != ''
    ORDER BY dtm.specialty, dtm.classification_name
  `).all();

  const standardTableNames = new Set();
  
  objectClasses.forEach(cls => {
    const specialtyPrefix = specialtyPrefixes[cls.specialty];
    if (specialtyPrefix) {
      const tableName = `device_instances_${specialtyPrefix}_${cls.english_name}`;
      standardTableNames.add(tableName);
    }
  });

  console.log(`标准实例表数量: ${standardTableNames.size}`);

  console.log('\n2. 识别需要清理的表...');
  
  // 获取所有实例表
  const allTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();

  const tablesToDelete = [];
  const tablesToKeep = [];
  const tablesWithData = [];

  allTables.forEach(table => {
    const tableName = table.name;
    
    if (standardTableNames.has(tableName)) {
      tablesToKeep.push(tableName);
    } else {
      // 检查表是否有数据
      try {
        const count = db.prepare(`SELECT COUNT(*) as count FROM [${tableName}]`).get();
        if (count.count > 0) {
          tablesWithData.push({
            name: tableName,
            count: count.count
          });
        } else {
          tablesToDelete.push(tableName);
        }
      } catch (error) {
        console.log(`  ⚠️  检查表 ${tableName} 失败: ${error.message}`);
        tablesToDelete.push(tableName);
      }
    }
  });

  console.log(`总实例表: ${allTables.length}`);
  console.log(`标准表(保留): ${tablesToKeep.length}`);
  console.log(`空的冗余表(删除): ${tablesToDelete.length}`);
  console.log(`有数据的非标准表: ${tablesWithData.length}`);

  if (tablesWithData.length > 0) {
    console.log('\n⚠️  以下非标准表包含数据:');
    tablesWithData.forEach(table => {
      console.log(`  ${table.name}: ${table.count} 条记录`);
    });
    
    // 尝试分析这些数据可以迁移到哪里
    console.log('\n3. 分析数据迁移可能性...');
    
    tablesWithData.forEach(table => {
      try {
        // 获取表中的设备类型信息
        const sample = db.prepare(`
          SELECT device_type_code, COUNT(*) as count
          FROM [${table.name}]
          GROUP BY device_type_code
          ORDER BY count DESC
          LIMIT 5
        `).all();
        
        console.log(`\n  ${table.name} 数据分析:`);
        sample.forEach(row => {
          // 查找对应的标准表
          const device = db.prepare(`
            SELECT d.*, dtm.english_name, dtm.specialty 
            FROM devices d
            LEFT JOIN device_type_mappings dtm ON d.classification_code = dtm.classification_code
            WHERE d.device_id = ?
          `).get(row.device_type_code);
          
          if (device && device.english_name && specialtyPrefixes[device.specialty]) {
            const targetTable = `device_instances_${specialtyPrefixes[device.specialty]}_${device.english_name}`;
            console.log(`    ${row.device_type_code} (${row.count}条) → ${targetTable}`);
          } else {
            console.log(`    ${row.device_type_code} (${row.count}条) → 未找到目标表`);
          }
        });
      } catch (error) {
        console.log(`    分析失败: ${error.message}`);
      }
    });
  }

  console.log('\n4. 执行清理操作...');
  
  let deletedCount = 0;
  const deleteTransaction = db.transaction(() => {
    tablesToDelete.forEach(tableName => {
      try {
        db.prepare(`DROP TABLE IF EXISTS [${tableName}]`).run();
        deletedCount++;
        if (deletedCount % 10 === 0) {
          console.log(`  已删除 ${deletedCount}/${tablesToDelete.length} 个表`);
        }
      } catch (error) {
        console.log(`  ✗ 删除表失败 ${tableName}: ${error.message}`);
      }
    });
  });

  deleteTransaction();

  console.log(`✓ 成功删除 ${deletedCount} 个空的冗余表`);

  console.log('\n5. 验证最终结果...');
  
  const finalTables = db.prepare(`
    SELECT name FROM sqlite_master 
    WHERE type='table' AND name LIKE 'device_instances_%'
    ORDER BY name
  `).all();

  const finalStandardTables = finalTables.filter(t => 
    standardTableNames.has(t.name)
  );

  const finalNonStandardTables = finalTables.filter(t => 
    !standardTableNames.has(t.name)
  );

  console.log(`\n📊 清理后统计:`);
  console.log(`- 总实例表数: ${finalTables.length}`);
  console.log(`- 标准格式表数: ${finalStandardTables.length}`);
  console.log(`- 非标准表数: ${finalNonStandardTables.length}`);
  
  if (finalNonStandardTables.length > 0) {
    console.log(`\n⚠️  剩余非标准表:`);
    finalNonStandardTables.forEach(table => {
      try {
        const count = db.prepare(`SELECT COUNT(*) as count FROM [${table.name}]`).get();
        console.log(`  ${table.name}: ${count.count} 条记录`);
      } catch (error) {
        console.log(`  ${table.name}: 检查失败`);
      }
    });
  }

  // 按专业统计标准表
  const specialtyStats = {};
  const specialtyTableMapping = {
    'piping': '配管专业',
    'electrical': '电气专业', 
    'hvac': '暖通专业',
    'communication': '通信专业',
    'instrumentation': '仪表专业'
  };

  finalStandardTables.forEach(table => {
    const tableName = table.name;
    let specialty = '其他';
    
    Object.entries(specialtyTableMapping).forEach(([prefix, chineseSpecialty]) => {
      if (tableName.includes(`_${prefix}_`)) {
        specialty = chineseSpecialty;
      }
    });
    
    if (!specialtyStats[specialty]) {
      specialtyStats[specialty] = 0;
    }
    specialtyStats[specialty]++;
  });

  console.log(`\n🏢 标准表按专业分布:`);
  Object.entries(specialtyStats).forEach(([specialty, count]) => {
    console.log(`  ${specialty}: ${count}个表`);
  });

  console.log('\n✅ 实例表清理完成!');
  
  if (finalNonStandardTables.length === 0) {
    console.log('🎉 所有实例表都已标准化!');
    console.log(`📋 最终结果: ${finalStandardTables.length} 个标准实例表，每个对应一个对象类`);
  } else {
    console.log(`💡 还有 ${finalNonStandardTables.length} 个非标准表包含数据，需要手动处理`);
  }

} catch (error) {
  console.error('❌ 清理过程中发生错误:', error.message);
  console.error(error.stack);
} finally {
  db.close();
}